﻿''' <summary>
''' Class to facilitate selecting and inserting records to/from data table (which must be using latest format)
''' </summary>
''' <remarks></remarks>
Public Class clsDataTable

    Public Project As clsProject
    Public TableName As String
    'note: in early versions of this class, transactions were automatically created on instantiation, but SQLite does not allow you to mix select and update/insert queries in same transactions, so made it explicit
    Private Trans As DbTransaction
    Private UpdateNeeded As Boolean = False
    Private cmdInsert As DbCommand

    Public Enum enumEndTransaction
        None
        Commit
        Rollback
    End Enum

    Public Sub New()
    End Sub

    ''' <summary>
    ''' Create new instance, open connection for all subsequent operations
    ''' </summary>
    ''' <param name="_Project">WRDB Project</param>
    ''' <param name="_TableName">Name of data table (must use current schema if exists, otherwise will be created)</param>
    Public Sub New(ByVal _Project As clsProject, ByVal _TableName As String)
        Project = _Project
        TableName = _TableName
        Project.ResetPDox(TableName)
        If Not (Project.DB.TableExists(TableName) Or Project.DB.ViewExists(TableName)) Then Project.DB.CreateTable(TableName, Project.GetTableTemplate(TableName), Project.DB.IsPrimaryKeyRequired)
        Project.DB.OpenConnection()
        'Trans = Project.DB.Connection.BeginTransaction
        Trans = Nothing

        'following are only used for InsertRecordFast
        With Project.DB
            cmdInsert = .Connection.CreateCommand
            Dim sqlFields As String = ""
            Dim sqlParams As String = ""
            Dim dt As DataTable = Project.GetTableTemplate(clsProject.enumTableTypes.Working)
            For i As Integer = 0 To dt.Columns.Count - 1
                Dim FieldName As String = dt.Columns(i).ColumnName.ToUpper
                Dim ParamName As String = .ParameterName(FieldName)
                sqlFields &= IIf(i = 0, "", ",") & FieldName
                sqlParams &= IIf(i = 0, "", ",") & ParamName
                cmdInsert.Parameters.Add(.Parameter(cmdInsert, ParamName, dt.Columns(i).DataType, dt.Columns(i).MaxLength))
            Next
            cmdInsert.CommandText = String.Format("INSERT INTO {0} ({1}) VALUES ({2})", .AddBrackets(TableName), sqlFields, sqlParams)
            'cmdInsert.Transaction = Trans
            cmdInsert.Connection = .Connection
            cmdInsert.Prepare()
        End With
    End Sub

    ''' <summary>
    ''' Begin transaction grouping following delete, update, and insert commands (automatically called by those routines if not done here); do not include select and count queries in same transaction as insert/update/delete queries (SQLite limitation)
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub BeginTransaction()
        Project.DB.OpenConnection()
        Trans = Project.DB.Connection.BeginTransaction
    End Sub

    ''' <summary>
    ''' End prior transaction, either committing or rolling all operations back
    ''' </summary>
    ''' <param name="EndTran">What to do with transaction operations</param>
    ''' <remarks></remarks>
    Public Sub EndTransaction(ByVal EndTran As enumEndTransaction)
        If Trans Is Nothing Then Exit Sub
        If Trans Is Nothing And EndTran <> enumEndTransaction.None Then Throw New Exception("EndTransaction called before BeginTransaction")
        Select Case EndTran
            Case enumEndTransaction.Commit
                Trans.Commit()
            Case enumEndTransaction.Rollback
                Trans.Rollback()
            Case enumEndTransaction.None
                'don't to anything
        End Select
        If Trans IsNot Nothing Then Trans.Dispose()
        'TransactionActive = False
        Trans = Nothing
    End Sub

    ''' <summary>
    ''' Return list of data records that meet certain criteria (do not include in transaction)
    ''' </summary>
    ''' <param name="WhereClause">Correctly formatted SQL WHERE clause (not including 'WHERE'); e.g., "PCode='ABC'"</param>
    ''' <param name="OrderClause">Correctly formatted SQL ORDER BY clause (not including 'ORDER BY'); e.g., "Date_Time"</param>
    ''' <returns>Nothing if error occurs</returns>
    Public Function SelectRecords(Optional ByVal WhereClause As String = "", Optional ByVal OrderClause As String = "") As Generic.List(Of clsDataRecord)
        Dim lstRecords As New Generic.List(Of clsDataRecord)

        Project.ResetPDox(TableName)
        Dim sql As String = String.Format("SELECT {0} FROM {1}", clsDataRecord.FieldList, TableName)
        If WhereClause <> "" Then sql &= String.Format(" WHERE {0}", WhereClause)
        If OrderClause <> "" Then sql &= String.Format(" ORDER BY {0}", OrderClause)
        Dim dr As DbDataReader = Project.DB.ExecuteReader(sql)
        If dr Is Nothing Then Return Nothing

        While dr.Read
            Dim ar(dr.FieldCount - 1) As Object
            dr.GetValues(ar)
            lstRecords.Add(New clsDataRecord(ar))
        End While
        dr.Close()
        Return lstRecords
    End Function

    ''' <summary>
    ''' Return list of distinct values for a specified field that meet certain criteria; result will be sorted (do not include in transaction)
    ''' </summary>
    ''' <param name="FieldName">Name of field to select</param>
    ''' <param name="WhereClause">Correctly formatted SQL WHERE clause (not including 'WHERE'); e.g., "PCode='ABC'"</param>
    ''' <returns>Nothing if error occurs</returns>
    Public Function SelectDistinctRecords(ByVal FieldName As String, Optional ByVal WhereClause As String = "") As Generic.List(Of String)
        Dim lstRecords As New Generic.List(Of String)

        Project.ResetPDox(TableName)
        Dim sql As String = String.Format("SELECT DISTINCT {0} FROM {1}", FieldName, TableName)
        If WhereClause <> "" Then sql &= String.Format(" WHERE {0}", WhereClause)
        sql &= String.Format(" ORDER BY {0}", FieldName)
        Dim dr As DbDataReader = Project.DB.ExecuteReader(sql)
        If dr Is Nothing Then Return Nothing

        While dr.Read
            lstRecords.Add(dr.GetValue(0))
        End While
        dr.Close()
        Return lstRecords
    End Function

    ''' <summary>
    ''' Return count of records meeting certain criteria (do not include in transaction)
    ''' </summary>
    ''' <param name="WhereClauseFormat">Correctly formatted SQL WHERE clause format (not including 'WHERE'); e.g., "PCode='{0}'"</param>
    ''' <param name="Args">Arguments if WhereClauseFormat is in string format with placeholders like {0}</param>
    ''' <returns>Number of records</returns>
    Public Function GetCount(ByVal WhereClauseFormat As String, ByVal ParamArray Args() As String) As Integer
        Project.ResetPDox(TableName)
        Dim sql As String = String.Format("SELECT COUNT(*) FROM {0} WHERE {1}", TableName, String.Format(WhereClauseFormat, Args))
        Return Project.DB.ExecuteScalar(sql)
    End Function

    ''' <summary>
    ''' Return count of records  (do not include in transaction)
    ''' </summary>
    ''' <returns>Number of records</returns>
    Public Function GetCount() As Integer
        If Trans IsNot Nothing Then Throw New ApplicationException("You must not have an open transaction when calling GetCount.")
        Project.ResetPDox(TableName)
        Dim sql As String = String.Format("SELECT COUNT(*) FROM {0} ", TableName)
        Return Project.DB.ExecuteScalar(sql)
    End Function

    ''' <summary>
    ''' Deletes records meeting certain criteria (may but need not be included in transaction)
    ''' </summary>
    ''' <param name="WhereClauseFormat">Correctly formatted SQL WHERE clause format (not including 'WHERE'); e.g., "PCode='{0}'"</param>
    ''' <param name="Args">Arguments if WhereClauseFormat is in string format with placeholders like {0}</param>
    Public Sub DeleteRecords(ByVal WhereClauseFormat As String, ByVal ParamArray Args() As String)
        Project.ResetPDox(TableName)
        Dim sql As String = String.Format("DELETE FROM {0} WHERE {1}", TableName, String.Format(WhereClauseFormat, Args))
        Project.DB.ExecuteNonQuery(Trans, sql)
    End Sub

    ''' <summary>
    ''' Insert or add a data record to the data table. Note that this will be done under a tranaction, so EndTransaction or Close MUST be called when all records have been added.
    ''' </summary>
    ''' <param name="DataRecord">Contents of data record to add</param>
    ''' <returns>True if successful</returns>
    Public Function InsertRecord(ByVal DataRecord As clsDataRecord) As Boolean
        'todo: use of parameters would be faster, but wouldn't be able to do calculations (e.g. Result * 10)
        'note: this may be problematic with Sqlite because of the way dates are stored (for EditInGrid)
        With Project.DB
            If Trans Is Nothing Then BeginTransaction()
            Dim sql As String
            sql = String.Format("INSERT INTO {0} ({1}) VALUES({2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14}, {15})",
                                TableName,
                                clsDataRecord.FieldList,
                                .TextString(DataRecord.StationID),
                                .DateTime(DataRecord.DateTime),
                                .TextString(DataRecord.PCode),
                                .TextString(DataRecord.CCode),
                                .TextString(DataRecord.LCode),
                                .TextString(DataRecord.SCode),
                                .TextString(DataRecord.QCode),
                                DataRecord.Result,
                                .TextString(DataRecord.RCode),
                                .Numeric(DataRecord.LEWPct),
                                .Numeric(DataRecord.DepthM),
                                .TextString(DataRecord.Agency),
                                .Logical(DataRecord.Validated),
                                .Numeric(DataRecord.TrackID))
            UpdateNeeded = True
            Debug.Assert(Trans IsNot Nothing, "Transaction was not started first!")
            'TransactionActive = True
            If .ExecuteNonQuery(Trans, sql) = -2 Then Return False
            Return True
        End With
    End Function

    ''' <summary>
    ''' Insert or add a data record to the data table using parameters (so cannot do calculations). Note that this will be done under a tranaction, so EndTransaction or Close MUST be called when all records have been added.
    ''' </summary>
    ''' <param name="DataRecord">Contents of data record to add</param>
    ''' <returns>True if successful</returns>
    Public Function InsertRecordFast(ByVal DataRecord As clsDataRecord) As Boolean
        With Project.DB
            UpdateNeeded = True
            If Trans Is Nothing Then BeginTransaction()
            cmdInsert.Transaction = Trans
            Dim dt As DataTable = Project.GetTableTemplate(clsProject.enumTableTypes.Working)
            For i As Integer = 0 To dt.Columns.Count - 1
                With DataRecord
                    Select Case i
                        Case 0 : If Not IsDBNull(.StationID) AndAlso Not String.IsNullOrEmpty(.StationID) Then cmdInsert.Parameters(i).Value = Left(.StationID, 25) Else cmdInsert.Parameters(i).Value = DBNull.Value
                        Case 1 : If Not IsDBNull(.DateTime) AndAlso .DateTime IsNot Nothing AndAlso IsDate(.DateTime) Then cmdInsert.Parameters(i).Value = .DateTime Else cmdInsert.Parameters(i).Value = DBNull.Value
                        Case 2 : If Not IsDBNull(.PCode) AndAlso Not String.IsNullOrEmpty(.PCode) Then cmdInsert.Parameters(i).Value = Left(.PCode, 10) Else cmdInsert.Parameters(i).Value = DBNull.Value
                        Case 3 : If Not IsDBNull(.LEWPct) AndAlso IsNumeric(.LEWPct) AndAlso .LEWPct <> Single.MinValue Then cmdInsert.Parameters(i).Value = .LEWPct Else cmdInsert.Parameters(i).Value = DBNull.Value
                        Case 4 : If Not IsDBNull(.DepthM) AndAlso IsNumeric(.DepthM) AndAlso .DepthM <> Single.MinValue Then cmdInsert.Parameters(i).Value = .DepthM Else cmdInsert.Parameters(i).Value = DBNull.Value
                        Case 5 : If Not IsDBNull(.Agency) AndAlso Not String.IsNullOrEmpty(.Agency) Then cmdInsert.Parameters(i).Value = Left(.Agency, 5) Else cmdInsert.Parameters(i).Value = DBNull.Value
                        Case 6 : If Not IsDBNull(.CCode) AndAlso Not String.IsNullOrEmpty(.CCode) Then cmdInsert.Parameters(i).Value = Left(.CCode, 3) Else cmdInsert.Parameters(i).Value = DBNull.Value
                        Case 7 : If Not IsDBNull(.LCode) AndAlso Not String.IsNullOrEmpty(.LCode) Then cmdInsert.Parameters(i).Value = Left(.LCode, 3) Else cmdInsert.Parameters(i).Value = DBNull.Value
                        Case 8 : If Not IsDBNull(.SCode) AndAlso Not String.IsNullOrEmpty(.SCode) Then cmdInsert.Parameters(i).Value = Left(.SCode, 3) Else cmdInsert.Parameters(i).Value = DBNull.Value
                        Case 9 : If Not IsDBNull(.QCode) AndAlso Not String.IsNullOrEmpty(.QCode) Then cmdInsert.Parameters(i).Value = Left(.QCode, 1) Else cmdInsert.Parameters(i).Value = DBNull.Value
                        Case 10 : If Not IsDBNull(.RCode) AndAlso Not String.IsNullOrEmpty(.RCode) Then cmdInsert.Parameters(i).Value = Left(.RCode, 1) Else cmdInsert.Parameters(i).Value = DBNull.Value
                        Case 11 : If Not IsDBNull(.Result) AndAlso IsNumeric(.Result) AndAlso .Result <> Single.MinValue Then cmdInsert.Parameters(i).Value = .Result Else cmdInsert.Parameters(i).Value = DBNull.Value
                        Case 12 : If Not IsDBNull(.Validated) AndAlso .Validated IsNot Nothing Then cmdInsert.Parameters(i).Value = .Validated Else cmdInsert.Parameters(i).Value = DBNull.Value
                        Case 13 : cmdInsert.Parameters(i).Value = Now
                        Case 14 : If Not IsDBNull(.TrackID) AndAlso .TrackID IsNot Nothing AndAlso IsNumeric(.TrackID) Then cmdInsert.Parameters(i).Value = .TrackID Else cmdInsert.Parameters(i).Value = DBNull.Value
                    End Select
                End With
            Next
            dt.Dispose()
            dt = Nothing
            Try
                cmdInsert.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
        End With
    End Function

    ''' <summary>
    ''' If data table contains Create_Update field, update entire table replacing null values with current date-time
    ''' </summary>
    Private Sub UpdateNullCreateUpdateFields()
        With Project.DB
            Try
                .ExecuteNonQuery(Trans, "UPDATE {0} SET CREATE_UPDATE={1} WHERE CREATE_UPDATE IS NULL", .FullTableName(TableName), .DateTime(Now))
            Catch ex As Exception
                'if failed because column doesn't exist, don't capture
            End Try
        End With
    End Sub

    ''' <summary>
    ''' Commit or rollback of the transaction and close the connection. This must always be called after InsertRecord calls
    ''' </summary>
    ''' <param name="EndTran">How should transaction be ended?</param>
    ''' <param name="UpdateCreateUpdateField">If true, will run long query to set value for CreateUpdate field (set to false for temporary tables)</param>
    Public Sub Close(Optional ByVal EndTran As enumEndTransaction = enumEndTransaction.None, Optional ByVal UpdateCreateUpdateField As Boolean = True)
        If Project.DB.Connection.State <> ConnectionState.Open Then Exit Sub
        If EndTran = enumEndTransaction.Commit AndAlso UpdateNeeded AndAlso UpdateCreateUpdateField Then UpdateNullCreateUpdateFields()
        EndTransaction(EndTran)
        Project.DB.Connection.Close()
    End Sub

End Class

''' <summary>
''' Class to facilitate selecting and inserting records to/from data table (which must be using latest format)
''' </summary>
''' <remarks>Result field can be handled as string in case want to do calculations during Insert operation</remarks>
Public Class clsDataRecord
    'following are objects so can hold dbnull values
    Public StationID, DateTime, PCode, LEWPct, DepthM, CCode, LCode, SCode, QCode, Agency, RCode, Result, Validated, TrackID As Object
    Friend Shared FieldList As String = "Station_ID, Date_Time, PCode, CCode, LCode, SCode, QCode, Result, RCode, LEW_Pct, Depth_M, Agency, Validated, Track_ID"

    Public Sub New()
    End Sub

    Public Sub New(ByVal _StationID As Object, ByVal _DateTime As Object, ByVal _PCode As Object, ByVal _Result As Object, Optional ByVal _CCode As Object = Nothing, Optional ByVal _RCode As Object = Nothing, Optional ByVal _LEWPct As Object = Nothing, Optional ByVal _DepthM As Object = Nothing, Optional ByVal _Agency As Object = Nothing, Optional ByVal _Validated As Object = Nothing, Optional ByVal _TrackID As Object = Nothing, Optional ByVal _LCode As Object = Nothing, Optional ByVal _SCode As Object = Nothing, Optional ByVal _QCode As Object = Nothing)
        StationID = TestNull(_StationID, Nothing)
        DateTime = TestNull(_DateTime, Nothing)
        PCode = TestNull(_PCode, Nothing)
        LEWPct = TestNull(_LEWPct, Single.MinValue)
        DepthM = TestNull(_DepthM, Single.MinValue)
        CCode = TestNull(_CCode, Nothing)
        Agency = TestNull(_Agency, Nothing)
        RCode = TestNull(_RCode, Nothing)
        Result = TestNull(_Result, Single.MinValue)
        Validated = TestNull(_Validated, Nothing)
        TrackID = TestNull(_TrackID, Nothing)
        LCode = TestNull(_LCode, Nothing)
        SCode = TestNull(_SCode, Nothing)
        QCode = TestNull(_QCode, Nothing)
    End Sub

    ''' <summary>
    ''' Fill data record using array of objects created using select query with fields in order indicated by Shared FieldList
    ''' </summary>
    ''' <param name="SelectValues"></param>
    ''' <remarks></remarks>
    Public Sub New(ByVal SelectValues() As Object)
        Dim i As Integer = 0
        StationID = TestNull(SelectValues(i), Nothing) : i += 1
        DateTime = TestNull(SelectValues(i), Nothing) : i += 1
        PCode = TestNull(SelectValues(i), Nothing) : i += 1
        CCode = TestNull(SelectValues(i), Nothing) : i += 1
        LCode = TestNull(SelectValues(i), Nothing) : i += 1
        SCode = TestNull(SelectValues(i), Nothing) : i += 1
        QCode = TestNull(SelectValues(i), Nothing) : i += 1
        Result = TestNull(SelectValues(i), Single.MinValue) : i += 1
        RCode = TestNull(SelectValues(i), Nothing) : i += 1
        LEWPct = TestNull(SelectValues(i), Single.MinValue) : i += 1
        DepthM = TestNull(SelectValues(i), Single.MinValue) : i += 1
        Agency = TestNull(SelectValues(i), Nothing) : i += 1
        Validated = TestNull(SelectValues(i), Nothing) : i += 1
        TrackID = TestNull(SelectValues(i), Nothing) : i += 1
    End Sub

End Class
